package com.egao.orderB.biz.impl;

import com.egao.common.core.web.BaseDao;
import com.egao.common.core.web.JsonResult;
import com.egao.common.core.web.PageResult2;
import com.egao.common.core.utils.DateUtil;
import com.egao.common.core.utils.Utils;
import com.egao.common.system.entity.User;
import com.egao.common.core.constants.UserConstant;
import com.egao.orderB.biz.BillBiz;
import com.egao.orderB.entity.Bill;
import net.sf.json.JSONObject;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.Map;
import java.util.List;

/**
 *
 * 数据层实现类
 * @author : Doman
 * @date : 2020-05-28 14:12:19
 * @version ：1.0
 */
@Repository
public class BillBizImpl extends BaseDao implements BillBiz {

	@Override
	public JSONObject list(PageResult2<T> page, Bill bill, User loginUser) throws Exception {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
		StringBuffer sql = new StringBuffer("select b.id,b.company_id,b.customer");
        sql.append(",b.contacts,b.contacts_phone,b.dock,b.province");
		sql.append(",b.address,b.weight,b.box_type,b.price");
		sql.append(",b.pack_date,b.vessel,b.voyage,b.bill_of_lading");
		sql.append(",b.factory_address,b.factory_phone,b.qr_code,b.car_team");
		sql.append(",b.driver,b.driver_phone,b.car_no,b.driver_freight,b.box_num");
		sql.append(",b.sealno,b.sealno_pic,b.box_num_pic,b.arrived_factory_latitude,b.arrived_factory_longitude");
		sql.append(",b.arrived_factory_date,b.arrived_factory_warn_date,b.leaved_factory_date,b.leaved_factory_longitude");
		sql.append(",b.leaved_factory_latitude,b.arrived_harbour_date,b.arrived_harbour_latitude,b.arrived_harbour_longitude");
		sql.append(",b.arrived_harbour_warn_date,b.annex,b.state,b.fee_state,b.pre_entry_pic");
		sql.append(",b.up_yard,b.back_yard,b.remark,b.create_time");
		sql.append(",b.offertime,b.maketime");
		sql.append(",b.arrival_port,b.sfile,b.white_card");
		sql.append(",b.driverReceivetime,b.upBoxtime,b.arrivedFactorytime,b.leaveFactorytime");
		sql.append(",b.inPorttime,b.costBilltime,b.verifytime,b.customerVerifytime");
		sql.append(",b.create_user,b.update_time,b.update_user");
        sql.append(",cmy.name companyName,suser.username createName ");
        sql.append(" from chf_bill b ");
        sql.append(" inner join sys_user suser on suser.user_id=b.create_user ");
        sql.append(" inner join chf_company cmy on cmy.id=b.company_id ");
        sql.append(" where b.deleted=0 and cmy.deleted=0 ");
		if(loginUser.getUserId() != UserConstant.ADMIN_ID){
            sql.append(" and b.company_id=:tablecompanyId ");
            map.put("tablecompanyId", loginUser.getCompanyId() + "");
        }
    	if(Utils.isNotEmpty(bill)){
			if(Utils.isNotEmpty(bill.getCustomer())){
				sql.append(" and instr(b.customer,:customer)>0 ");
				map.put("customer", bill.getCustomer());
			}
			if(Utils.isNotEmpty(bill.getContacts())){
				sql.append(" and instr(b.contacts,:contacts)>0 ");
				map.put("contacts", bill.getContacts());
			}
			if(Utils.isNotEmpty(bill.getBillOfLading())){
				sql.append(" and instr(b.bill_of_lading,:bill_of_lading)>0 ");
				map.put("bill_of_lading", bill.getBillOfLading());
			}
			if(Utils.isNotEmpty(bill.getCarNo())){
				sql.append(" and instr(b.car_no,:car_no)>0 ");
				map.put("car_no", bill.getCarNo());
			}
			if(Utils.isNotEmpty(bill.getDriver())){
				sql.append(" and instr(b.driver,:driver)>0 ");
				map.put("driver", bill.getDriver());
			}
			if(Utils.isNotEmpty(bill.getDriverPhone())){
				sql.append(" and instr(b.driver_phone,:driver_phone)>0 ");
				map.put("driver_phone", bill.getDriverPhone());
			}
			if(Utils.isNotEmpty(bill.getBoxNum())){
				sql.append(" and instr(b.box_num,:box_num)>0 ");
				map.put("box_num", bill.getBoxNum());
			}
			if(Utils.isNotEmpty(bill.getFactoryAddress())){
				sql.append(" and instr(b.factory_address,:factory_address)>0 ");
				map.put("factory_address", bill.getFactoryAddress());
			}
			if(Utils.isNotEmpty(bill.getState())){
				sql.append(" and instr(b.state,:state)>0 ");
				map.put("state", bill.getState());
			}
			if(Utils.isNotEmpty(bill.getFeeState())){
				sql.append(" and instr(b.fee_state,:fee_state)>0 ");
				map.put("fee_state", bill.getFeeState());
			}
			if(Utils.isNotEmpty(bill.getDate1())){
				sql.append(" and b.pack_date>= :date1 ");
				map.put("date1", bill.getDate1());
			}
			if(Utils.isNotEmpty(bill.getDate2())){
				sql.append(" and b.pack_date<=:date2 ");
				map.put("date2", bill.getDate2());
			}
			if(Utils.isNotEmpty(bill.getCreateName())){
				sql.append(" and instr(b.createName,:createName)>0 ");
				map.put("createName", bill.getCreateName());
			}
			if(Utils.isNotEmpty(bill.getWhiteCard())){
				sql.append(" and instr(b.white_card,:white_card)>0 ");
				map.put("white_card", bill.getWhiteCard());
			}
			if(Utils.isNotEmpty(bill.getArrivalPort())){
				sql.append(" and instr(b.arrival_port,:arrival_port)>0 ");
				map.put("arrival_port", bill.getArrivalPort());
			}
			if(Utils.isNotEmpty(bill.getSort())&&Utils.isNotEmpty(bill.getOrder())){
				sql.append(" order by "+bill.getSort()+" "+bill.getOrder());
			}
		}
		return jsonPage2(getPageQueryList(page, sql.toString(), map, rm));
	}

	@Override
	public List<Bill> list(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
        StringBuffer sql = new StringBuffer("select b.id,b.company_id,b.customer");
        sql.append(",b.contacts,b.contacts_phone,b.dock,b.province");
		sql.append(",b.address,b.weight,b.box_type,b.price");
		sql.append(",b.pack_date,b.vessel,b.voyage,b.bill_of_lading");
		sql.append(",b.factory_address,b.factory_phone,b.qr_code,b.car_team");
		sql.append(",b.driver,b.driver_phone,b.car_no,b.driver_freight,b.box_num");
		sql.append(",b.sealno,b.sealno_pic,b.box_num_pic,b.arrived_factory_latitude,b.arrived_factory_longitude");
		sql.append(",b.arrived_factory_date,b.arrived_factory_warn_date,b.leaved_factory_date,b.leaved_factory_longitude");
		sql.append(",b.leaved_factory_latitude,b.arrived_harbour_date,b.arrived_harbour_latitude,b.arrived_harbour_longitude");
		sql.append(",b.arrived_harbour_warn_date,b.annex,b.state,b.fee_state,b.pre_entry_pic");
		sql.append(",b.up_yard,b.back_yard,b.remark,b.create_time");
		sql.append(",b.offertime,b.maketime");
		sql.append(",b.arrival_port,b.sfile,b.white_card");
		sql.append(",b.driverReceivetime,b.upBoxtime,b.arrivedFactorytime,b.leaveFactorytime");
		sql.append(",b.inPorttime,b.costBilltime,b.verifytime,b.customerVerifytime");
		sql.append(",b.create_user,b.update_time,b.update_user");
        sql.append(",cmy.name companyName,suser.username createName ");
        sql.append(" from chf_bill b ");
        sql.append(" inner join sys_user suser on suser.user_id=b.create_user ");
        sql.append(" inner join chf_company cmy on cmy.id=b.company_id ");
        sql.append(" where b.deleted=0 and cmy.deleted=0 ");
		if(loginUser.getUserId() != UserConstant.ADMIN_ID){
            sql.append(" and b.company_id=:tablecompanyId ");
            map.put("tablecompanyId", loginUser.getCompanyId() + "");
        }
		if(Utils.isNotEmpty(bill)){
			if(Utils.isNotEmpty(bill.getCustomer())){
				sql.append(" and instr(b.customer,:customer)>0 ");
				map.put("customer", bill.getCustomer());
			}
			if(Utils.isNotEmpty(bill.getContacts())){
				sql.append(" and instr(b.contacts,:contacts)>0 ");
				map.put("contacts", bill.getContacts());
			}
			if(Utils.isNotEmpty(bill.getBillOfLading())){
				sql.append(" and instr(b.bill_of_lading,:bill_of_lading)>0 ");
				map.put("bill_of_lading", bill.getBillOfLading());
			}
			if(Utils.isNotEmpty(bill.getCarNo())){
				sql.append(" and instr(b.car_no,:car_no)>0 ");
				map.put("car_no", bill.getCarNo());
			}
			if(Utils.isNotEmpty(bill.getDriver())){
				sql.append(" and instr(b.driver,:driver)>0 ");
				map.put("driver", bill.getDriver());
			}
			if(Utils.isNotEmpty(bill.getDriverPhone())){
				sql.append(" and instr(b.driver_phone,:driver_phone)>0 ");
				map.put("driver_phone", bill.getDriverPhone());
			}
			if(Utils.isNotEmpty(bill.getBoxNum())){
				sql.append(" and instr(b.box_num,:box_num)>0 ");
				map.put("box_num", bill.getBoxNum());
			}
			if(Utils.isNotEmpty(bill.getFactoryAddress())){
				sql.append(" and instr(b.factory_address,:factory_address)>0 ");
				map.put("factory_address", bill.getFactoryAddress());
			}
			if(Utils.isNotEmpty(bill.getState())){
				sql.append(" and instr(b.state,:state)>0 ");
				map.put("state", bill.getState());
			}
			if(Utils.isNotEmpty(bill.getFeeState())){
				sql.append(" and instr(b.fee_state,:fee_state)>0 ");
				map.put("fee_state", bill.getFeeState());
			}
			if(Utils.isNotEmpty(bill.getDate1())){
				sql.append(" and b.pack_date>= :date1 ");
				map.put("date1", bill.getDate1());
			}
			if(Utils.isNotEmpty(bill.getDate2())){
				sql.append(" and b.pack_date<=:date2 ");
				map.put("date2", bill.getDate2());
			}
			if(Utils.isNotEmpty(bill.getCreateName())){
				sql.append(" and instr(b.createName,:createName)>0 ");
				map.put("createName", bill.getCreateName());
			}
			if(Utils.isNotEmpty(bill.getWhiteCard())){
				sql.append(" and instr(b.white_card,:white_card)>0 ");
				map.put("white_card", bill.getWhiteCard());
			}
			if(Utils.isNotEmpty(bill.getArrivalPort())){
				sql.append(" and instr(b.arrival_port,:arrival_port)>0 ");
				map.put("arrival_port", bill.getArrivalPort());
			}
			if(Utils.isNotEmpty(bill.getSort())&&Utils.isNotEmpty(bill.getOrder())){
				sql.append(" order by "+bill.getSort()+" "+bill.getOrder());
			}
		}
		getMapParameter(sql, map);
		return jdbcTemplate.query(sql.toString(),map, rm);
	}

	@Override
	public Bill getById(Bill bill) {
		RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("select b.id,b.company_id,b.customer");
        sql.append(",b.contacts,b.contacts_phone,b.dock,b.province");
		sql.append(",b.address,b.weight,b.box_type,b.price");
		sql.append(",b.pack_date,b.vessel,b.voyage,b.bill_of_lading");
		sql.append(",b.factory_address,b.factory_phone,b.qr_code,b.car_team");
		sql.append(",b.driver,b.driver_phone,b.car_no,b.driver_freight,b.box_num");
		sql.append(",b.sealno,b.sealno_pic,b.box_num_pic,b.arrived_factory_latitude,b.arrived_factory_longitude");
		sql.append(",b.arrived_factory_date,b.arrived_factory_warn_date,b.leaved_factory_date,b.leaved_factory_longitude");
		sql.append(",b.leaved_factory_latitude,b.arrived_harbour_date,b.arrived_harbour_latitude,b.arrived_harbour_longitude");
		sql.append(",b.arrived_harbour_warn_date,b.annex,b.state,b.fee_state,b.pre_entry_pic");
		sql.append(",b.up_yard,b.back_yard,b.remark,b.create_time");
		sql.append(",b.offertime,b.maketime");
		sql.append(",b.arrival_port,b.sfile,b.white_card");
		sql.append(",b.driverReceivetime,b.upBoxtime,b.arrivedFactorytime,b.leaveFactorytime");
		sql.append(",b.inPorttime,b.costBilltime,b.verifytime,b.customerVerifytime");
		sql.append(",b.create_user,b.update_time,b.update_user");
        sql.append(",cmy.name companyName,suser.username createName ");
        sql.append(" from chf_bill b ");
        sql.append(" inner join sys_user suser on suser.user_id=b.create_user ");
        sql.append(" inner join chf_company cmy on cmy.id=b.company_id ");
        sql.append(" where b.deleted=0 and cmy.deleted=0 and b.id=:id ");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		return jdbcTemplate.queryForObject(sql.toString(), map, rm);
	}

	@Override
	public boolean save(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("insert into chf_bill (");
        sql.append("company_id,customer,contacts,contacts_phone");
		sql.append(",dock,province,address,weight");
		sql.append(",box_type,price,pack_date,vessel");
		sql.append(",voyage,bill_of_lading,factory_address,factory_phone");
		sql.append(",qr_code,car_team,driver,driver_phone");
		sql.append(",car_no,driver_freight,box_num,sealno,sealno_pic,box_num_pic");
		sql.append(",arrived_factory_latitude,arrived_factory_longitude,arrived_factory_date,arrived_factory_warn_date");
		sql.append(",leaved_factory_date,leaved_factory_longitude,leaved_factory_latitude,arrived_harbour_date");
		sql.append(",arrived_harbour_latitude,arrived_harbour_longitude,arrived_harbour_warn_date,annex,state,fee_state,pre_entry_pic");
		sql.append(",up_yard,back_yard");
		sql.append(",arrival_port,sfile,white_card");
		sql.append(",remark,create_user) values ( ");
        sql.append(":company_id,:customer,:contacts,:contacts_phone");
		sql.append(",:dock,:province,:address,:weight");
		sql.append(",:box_type,:price,:pack_date,:vessel");
		sql.append(",:voyage,:bill_of_lading,:factory_address,:factory_phone");
		sql.append(",:qr_code,:car_team,:driver,:driver_phone");
		sql.append(",:car_no,:driver_freight,:box_num,:sealno,:sealno_pic,:box_num_pic");
		sql.append(",:arrived_factory_latitude,:arrived_factory_longitude,:arrived_factory_date,:arrived_factory_warn_date");
		sql.append(",:leaved_factory_date,:leaved_factory_longitude,:leaved_factory_latitude,:arrived_harbour_date");
		sql.append(",:arrived_harbour_latitude,:arrived_harbour_longitude,:arrived_harbour_warn_date,:annex,:state,:fee_state,:pre_entry_pic");
		sql.append(",:up_yard,:back_yard");
		sql.append(",:arrival_port,:sfile,:white_card");
		sql.append(",:remark,:create_user)");
        map.put("company_id", loginUser.getCompanyId() + "");
        map.put("customer", bill.getCustomer());
        map.put("contacts", bill.getContacts());
        map.put("contacts_phone", bill.getContactsPhone());
        map.put("dock", bill.getDock());
        map.put("province", bill.getProvince());
        map.put("address", bill.getAddress());
        map.put("weight", bill.getWeight());
        map.put("box_type", bill.getBoxType());
        map.put("price", bill.getPrice());
        map.put("pack_date", bill.getPackDate());
        map.put("vessel", bill.getVessel());
        map.put("voyage", bill.getVoyage());
        map.put("bill_of_lading", bill.getBillOfLading());
        map.put("factory_address", bill.getFactoryAddress());
        map.put("factory_phone", bill.getFactoryPhone());
        map.put("qr_code", bill.getQrCode());
        map.put("car_team", bill.getCarTeam());
        map.put("driver", bill.getDriver());
        map.put("driver_phone", bill.getDriverPhone());
        map.put("car_no", bill.getCarNo());
        map.put("driver_freight", bill.getDriverFreight());
        map.put("box_num", bill.getBoxNum());
		map.put("box_num_pic", bill.getBoxNumPic());
		map.put("sealno", bill.getSealno());
		map.put("sealno_pic", bill.getSealnoPic());
        map.put("arrived_factory_latitude", bill.getArrivedFactoryLatitude());
        map.put("arrived_factory_longitude", bill.getArrivedFactoryLongitude());
        map.put("arrived_factory_date", bill.getArrivedFactoryDate());
        map.put("arrived_factory_warn_date", bill.getArrivedFactoryWarnDate());
        map.put("leaved_factory_date", bill.getLeavedFactoryDate());
        map.put("leaved_factory_longitude", bill.getLeavedFactoryLongitude());
        map.put("leaved_factory_latitude", bill.getLeavedFactoryLatitude());
        map.put("arrived_harbour_date", bill.getArrivedHarbourDate());
        map.put("arrived_harbour_latitude", bill.getArrivedHarbourLatitude());
        map.put("arrived_harbour_longitude", bill.getArrivedHarbourLongitude());
        map.put("arrived_harbour_warn_date", bill.getArrivedHarbourWarnDate());
        map.put("pre_entry_pic", bill.getPreEntryPic());
        map.put("annex", bill.getAnnex());
        map.put("state", bill.getState());
        map.put("fee_state", bill.getFeeState());
        map.put("up_yard", bill.getUpYard());
        map.put("back_yard", bill.getBackYard());
        map.put("arrival_port", bill.getArrivalPort());
        map.put("sfile", bill.getSfile());
        map.put("white_card", bill.getWhiteCard());
        map.put("remark", bill.getRemark());
		map.put("create_user", loginUser.getUserId() + "");
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public Integer order(Bill bill, User loginUser) {
		GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
		KeyHolder keyHolder=new GeneratedKeyHolder();
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("insert into chf_bill (");
		sql.append("company_id,customer,contacts,contacts_phone");
		sql.append(",dock,province,address,weight");
		sql.append(",box_type,price,pack_date,vessel");
		sql.append(",voyage,bill_of_lading,factory_address,factory_phone");
		sql.append(",arrived_factory_warn_date,arrived_harbour_warn_date");
		sql.append(",qr_code,annex,state,fee_state,offertime");
		sql.append(",arrival_port,sfile,white_card");
		sql.append(",remark,create_user)");
		sql.append(" values (");
		sql.append(":company_id,:customer,:contacts,:contacts_phone");
		sql.append(",:dock,:province,:address,:weight");
		sql.append(",:box_type,:price,:pack_date,:vessel");
		sql.append(",:voyage,:bill_of_lading,:factory_address,:factory_phone");
		sql.append(",:arrived_factory_warn_date,:arrived_harbour_warn_date");
		sql.append(",:qr_code,:annex,:state,:fee_state,now()");
		sql.append(",:arrival_port,:sfile,:white_card");
		sql.append(",:remark,:create_user)");
		map.put("company_id", loginUser.getCompanyId() + "");
		map.put("customer", bill.getCustomer());
		map.put("contacts", bill.getContacts());
		map.put("contacts_phone", bill.getContactsPhone());
		map.put("dock", bill.getDock());
		map.put("province", bill.getProvince());
		map.put("address", bill.getAddress());
		map.put("weight", bill.getWeight());
		map.put("box_type", bill.getBoxType());
		map.put("price", bill.getPrice());
		map.put("pack_date", bill.getPackDate());
		map.put("vessel", bill.getVessel());
		map.put("voyage", bill.getVoyage());
		map.put("bill_of_lading", bill.getBillOfLading());
		map.put("factory_address", bill.getFactoryAddress());
		map.put("factory_phone", bill.getFactoryPhone());
		map.put("qr_code", bill.getQrCode());
		map.put("arrived_factory_warn_date", bill.getArrivedFactoryWarnDate());
		map.put("arrived_harbour_warn_date", bill.getArrivedHarbourWarnDate());
		map.put("annex", bill.getAnnex());
		map.put("state", bill.getState());
		map.put("fee_state", bill.getFeeState());
		map.put("arrival_port", bill.getArrivalPort());
		map.put("sfile", bill.getSfile());
		map.put("white_card", bill.getWhiteCard());
		map.put("remark", bill.getRemark());
		map.put("create_user", loginUser.getUserId() + "");
		getMapParameter(sql, map);
		//		System.err.println("受到影响的行数=" + retVal + ",
		int retVal = this.jdbcTemplate.update(sql.toString(), new SqlParameterSource() {
			@Override
			public boolean hasValue(String paramName) {
				return map.containsKey(paramName);
			}
			@Override
			public Object getValue(String paramName) throws IllegalArgumentException {
				return map.get(paramName);
			}
		}, generatedKeyHolder);

		Integer id = generatedKeyHolder.getKey().intValue();
		return id;
	}

	@Override
	public boolean make(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("car_team=:car_team,driver=:driver ");
		sql.append(",driver_freight=:driver_freight");
		sql.append(",white_card=:white_card");
		sql.append(",sfile=:sfile");
		sql.append(",driver_phone=:driver_phone,car_no=:car_no" +
				",state=:state,maketime=now(),remark=:remark ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("car_team", bill.getCarTeam());
		map.put("driver", bill.getDriver());
		map.put("driver_freight", bill.getDriverFreight());
		map.put("white_card", bill.getWhiteCard());
		map.put("sfile", bill.getSfile());
		map.put("driver_phone", bill.getDriverPhone());
		map.put("car_no", bill.getCarNo());
		map.put("state", bill.getState());
		map.put("remark", bill.getRemark());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean driverReceive(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append(" state=:state,driverReceivetime=now() ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("state", bill.getState());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean upBox(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("box_num=:box_num,arrival_port=:arrival_port,up_yard=:up_yard ");
		sql.append(",sealno=:sealno,sealno_pic=:sealno_pic,box_num_pic=:box_num_pic" +
				",state=:state,upBoxtime=now(),remark=:remark ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("box_num", bill.getBoxNum());
		map.put("arrival_port", bill.getArrivalPort());
		map.put("up_yard", bill.getUpYard());
		map.put("box_num_pic", bill.getBoxNumPic());
		map.put("sealno", bill.getSealno());
		map.put("sealno_pic", bill.getSealnoPic());
		map.put("state", bill.getState());
		map.put("remark", bill.getRemark());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean arrivedFactory(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("arrived_factory_latitude=:arrived_factory_latitude ");
		sql.append(",arrived_factory_longitude=:arrived_factory_longitude," +
				"arrived_factory_date=:arrived_factory_date ");
		sql.append(",state=:state,arrivedFactorytime=now(),remark=:remark ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("arrived_factory_latitude", bill.getArrivedFactoryLatitude());
		map.put("arrived_factory_longitude", bill.getArrivedFactoryLongitude());
		map.put("arrived_factory_date", bill.getArrivedFactoryDate());
		map.put("state", bill.getState());
		map.put("remark", bill.getRemark());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean leaveFactory(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("leaved_factory_date=:leaved_factory_date,leaved_factory_longitude=:leaved_factory_longitude," +
				"leaved_factory_latitude=:leaved_factory_latitude ");
		sql.append(",state=:state,leaveFactorytime=now(),remark=:remark ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("leaved_factory_date", bill.getLeavedFactoryDate());
		map.put("leaved_factory_longitude", bill.getLeavedFactoryLongitude());
		map.put("leaved_factory_latitude", bill.getLeavedFactoryLatitude());
		map.put("state", bill.getState());
		map.put("remark", bill.getRemark());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean inPort(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("arrived_harbour_date=:arrived_harbour_date,arrived_harbour_latitude=:arrived_harbour_latitude," +
				"arrived_harbour_longitude=:arrived_harbour_longitude,pre_entry_pic=:pre_entry_pic ");
		sql.append(",state=:state,inPorttime=now(),remark=:remark ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("arrived_harbour_date", bill.getArrivedHarbourDate());
		map.put("arrived_harbour_latitude", bill.getArrivedHarbourLatitude());
		map.put("arrived_harbour_longitude", bill.getArrivedHarbourLongitude());
		map.put("pre_entry_pic", bill.getPreEntryPic());
		map.put("state", bill.getState());
		map.put("remark", bill.getRemark());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean costBill(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("state=:state,costBilltime=now(),remark=:remark ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("state", bill.getState());
		map.put("remark", bill.getRemark());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean verify(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("state=:state,verifytime=now(),remark=:remark ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("state", bill.getState());
		map.put("remark", bill.getRemark());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean driverFeeVerify(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("state=:state,driverFeeVerifytime=now(),remark=:remark ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("state", bill.getState());
		map.put("remark", bill.getRemark());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean customerVerify(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("state=:state,customerVerifytime=now(),remark=:remark ");
		sql.append(",update_user=:update_user where id=:id ");
		map.put("state", bill.getState());
		map.put("remark", bill.getRemark());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean update(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("customer=:customer,contacts=:contacts,contacts_phone=:contacts_phone");
		sql.append(",dock=:dock,province=:province,address=:address,weight=:weight");
		sql.append(",box_type=:box_type,price=:price,pack_date=:pack_date,vessel=:vessel");
		sql.append(",voyage=:voyage,bill_of_lading=:bill_of_lading,factory_address=:factory_address" +
				",factory_phone=:factory_phone");
		sql.append(",qr_code=:qr_code,car_team=:car_team,driver=:driver");
		sql.append(",arrival_port=:arrival_port,sfile=:sfile,white_card=:white_card");
		sql.append(",driver_phone=:driver_phone,car_no=:car_no,driver_freight=:driver_freight,box_num=:box_num");
		sql.append(",box_num_pic=:box_num_pic,sealno=:sealno,sealno_pic=:sealno_pic");
		sql.append(",arrived_factory_latitude=:arrived_factory_latitude" +
				",arrived_factory_longitude=:arrived_factory_longitude" +
				",arrived_factory_date=:arrived_factory_date");
		sql.append(",arrived_factory_warn_date=:arrived_factory_warn_date");
		sql.append(",leaved_factory_date=:leaved_factory_date" +
				",leaved_factory_longitude=:leaved_factory_longitude" +
				",leaved_factory_latitude=:leaved_factory_latitude");
		sql.append(",arrived_harbour_date=:arrived_harbour_date" +
				",arrived_harbour_latitude=:arrived_harbour_latitude" +
				",arrived_harbour_longitude=:arrived_harbour_longitude");
		sql.append(",arrived_harbour_warn_date=:arrived_harbour_warn_date");
		sql.append(",pre_entry_pic=:pre_entry_pic,annex=:annex,remark=:remark");
		sql.append(",up_yard=:up_yard,back_yard=:back_yard");
		sql.append(",update_user=:update_user where id=:id ");
        map.put("customer", bill.getCustomer());
        map.put("contacts", bill.getContacts());
        map.put("contacts_phone", bill.getContactsPhone());
        map.put("dock", bill.getDock());
        map.put("province", bill.getProvince());
        map.put("address", bill.getAddress());
        map.put("weight", bill.getWeight());
        map.put("box_type", bill.getBoxType());
        map.put("price", bill.getPrice());
        map.put("pack_date", bill.getPackDate());
        map.put("vessel", bill.getVessel());
        map.put("voyage", bill.getVoyage());
        map.put("bill_of_lading", bill.getBillOfLading());
        map.put("factory_address", bill.getFactoryAddress());
        map.put("factory_phone", bill.getFactoryPhone());
        map.put("qr_code", bill.getQrCode());
        map.put("car_team", bill.getCarTeam());
        map.put("driver", bill.getDriver());
        map.put("arrival_port", bill.getArrivalPort());
        map.put("sfile", bill.getSfile());
        map.put("white_card", bill.getWhiteCard());
        map.put("driver_phone", bill.getDriverPhone());
        map.put("car_no", bill.getCarNo());
        map.put("driver_freight", bill.getDriverFreight());
		map.put("box_num", bill.getBoxNum());
		map.put("box_num_pic", bill.getBoxNumPic());
        map.put("sealno", bill.getSealno());
		map.put("sealno_pic", bill.getSealnoPic());
        map.put("arrived_factory_latitude", bill.getArrivedFactoryLatitude());
        map.put("arrived_factory_longitude", bill.getArrivedFactoryLongitude());
        map.put("arrived_factory_date", bill.getArrivedFactoryDate());
        map.put("arrived_factory_warn_date", bill.getArrivedFactoryWarnDate());
        map.put("leaved_factory_date", bill.getLeavedFactoryDate());
        map.put("leaved_factory_longitude", bill.getLeavedFactoryLongitude());
        map.put("leaved_factory_latitude", bill.getLeavedFactoryLatitude());
        map.put("arrived_harbour_date", bill.getArrivedHarbourDate());
        map.put("arrived_harbour_latitude", bill.getArrivedHarbourLatitude());
        map.put("arrived_harbour_longitude", bill.getArrivedHarbourLongitude());
        map.put("arrived_harbour_warn_date", bill.getArrivedHarbourWarnDate());
        map.put("pre_entry_pic", bill.getPreEntryPic());
        map.put("annex", bill.getAnnex());
		map.put("up_yard", bill.getUpYard());
		map.put("back_yard", bill.getBackYard());
        map.put("remark", bill.getRemark());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean feeState(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update chf_bill set ");
		sql.append("fee_state=:fee_state,remark=:remark");
		sql.append(",update_user=:update_user,update_time=now() where id=:id ");
        map.put("fee_state", bill.getFeeState());
        map.put("remark", bill.getRemark());
		map.put("update_user", loginUser.getUserId() + "");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		int count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean remove(Bill bill, User loginUser) {
		int count = 0;
		Map<String, String> map = null;
		if(bill.getId().length()>0){
			for(String ID : bill.getId().split(",")){
				map  = new HashMap<String, String>();
				if(ID==null || ID.equals("")){ break; }
				StringBuffer sql =new StringBuffer("update chf_bill set deleted=1" +
						",update_user=:update_user, update_time=now() "
						+ " where id =:id ");
				map.put("update_user", loginUser.getUserId() + "");
				map.put("id", ID);
				getMapParameter(sql, map);
				count = jdbcTemplate.update(sql.toString(),map);
			}
		}
		return count > 0;
	}
}
